package dbAccess;

import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.apache.commons.dbcp.BasicDataSource;

public class DbAccessSession {

	
	private Logger logger =Logger.getLogger(DbAccessSession.class.getName());
	private BasicDataSource dataSource=null;
	private Connection connection=null;
	
	public static SimpleDateFormat sdf =    new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	
	//This map stores the properties of all the bean objects used for data access
	//It is populated using reflection only first time
	private static Map<String,List<DbProperty>> ObjectMap=new HashMap<String, List<DbProperty>>();
	
	public DbAccessSession(BasicDataSource ds) {
		this.dataSource=ds;
	}
	
	
	
	public void openDbSession() throws SQLException{
		connection =this.dataSource.getConnection();
		
	}
	
	public void closeDbSession() throws SQLException{
		this.connection.close();
	}
	
	
	public void commitDbSession() throws SQLException{
		this.connection.commit();
	}
	
	public void rollBackDbSession() throws SQLException{
		this.connection.rollback();
	}
	
	/**
	 *This method creates a record in the database and update the auto increment column in any present
	 * @param className
	 * @param input
	 * @throws SQLException 
	 */
	
	public void insert(String tableName, IDbAccessObject input) throws SQLException{
		
		
		StringBuffer vars=new StringBuffer();
		StringBuffer vals=new StringBuffer();
		List<DbProperty> propList = input.getInput();
		
		for( DbProperty prop:propList){
			vars.append(",".intern()).append(prop.getPropertyName());
			vals.append(", ?".intern());	
		}
			
		String str ="INSERT INTO ".intern()+tableName+"("+vars.toString().substring(1)+" ) VALUES ( ".intern()+vals.toString().substring(1)+")".intern();		
		PreparedStatement  stmt = connection.prepareStatement(str,Statement.RETURN_GENERATED_KEYS);
		int i=1;
		for( DbProperty prop:propList){		
				stmt.setObject(i++, prop.getValue());
		}
		logger.info(str.toString());
		int result =stmt.executeUpdate();
		if(result==1) {
			   ResultSet rs = stmt.getGeneratedKeys();
			    rs.next();	 
		           List<DbProperty> list=new ArrayList<DbProperty>();
		           logger.fine("Generated Id "+rs.getObject(1));
		           list.add(new DbProperty("autoIncrementKey".intern(),rs.getObject(1)));
		         
		           
		          input.setObject(list);
		          rs.close();
			}
			
		stmt.close();
		
		
		
		
	}
	
	public void update(String tableName, IDbAccessObject input) throws SQLException{
		this.update(tableName, input, null);
	}
	
	
	/**
	 * This method performs the update operations
	 * @param className
	 * @param input
	 * @param condition
	 * @throws SQLException
	 */
	public void update(String tableName, IDbAccessObject input, IDbAccessObject condition) throws SQLException{
		
		if(input==null){
			logger.log(Level.INFO,"No records found to updated");
			return;
		}
		
		//Get the update statement
		StringBuffer update=new StringBuffer("UPDATE ".intern()).append(tableName).append(" SET ".intern());		
        List<DbProperty> propList = input.getInput();	
		update.append(getStatement(propList," , ","="));
		//Get the condition for update if any
		if(condition!=null && condition.getInput()!=null && condition.getInput().size()>0){
			update.append(" WHERE ");
			update.append(getStatement(condition.getInput()," AND "," LIKE "));
		}
		
		PreparedStatement stmt = connection.prepareStatement(update.toString());
		int i=1;
		//populate the statement parameters
		for( DbProperty prop:propList){		
			stmt.setObject(i++, prop.getValue());
		}
		if(condition!=null && condition.getInput()!=null && condition.getInput().size()>0){
			for( DbProperty prop:condition.getInput()){		
				stmt.setObject(i++, prop.getValue());
			}
			
		}
				
		logger.log(Level.INFO,"Executing query: "+update.toString());
		int result=stmt.executeUpdate();
		logger.log(Level.INFO,result+ " records updated");
		stmt.close();
		
	}
	
	
	/**
	 * This method returns a concatenated string from arraylist properties
	 * @param propList
	 * @param delimitter
	 * @return
	 */
	
	
	private Object getStatement(List<DbProperty> propList, String delimitter, String type) {
		StringBuffer str=new StringBuffer();
		for( DbProperty prop:propList){
			str.append(delimitter).append(prop.getPropertyName());
		
			 if(prop.getValue() instanceof String){
				 str.append(type).append("?");
			}
			 else{
				 str.append(" = ?");
			 }
			
		}
		
		return str.toString().substring(delimitter.length());
		
	}
	
	
	/**
	 * This method performs the delete operations
	 * @param className
	 * @param input
	 * @throws SQLException
	 */
    public void delete(String tableName, IDbAccessObject input) throws SQLException{
    	
    	StringBuffer deleteStr=new StringBuffer("DELETE FROM ").append(tableName);
    	if(input!=null){
    		deleteStr.append(" WHERE ");
    		deleteStr.append(getStatement(input.getInput()," AND "," LIKE "));
    	}
    	
    	
    	PreparedStatement stmt = connection.prepareStatement(deleteStr.toString());
    	if(input!=null && input.getInput()!=null){
	    	int i=1;
			//populate the statement parameters
			for( DbProperty prop:input.getInput()){		
				stmt.setObject(i++, prop.getValue());
			}
    	}
    	
    	
		logger.log(Level.INFO,"Executing query: "+stmt.toString());
		int result=stmt.executeUpdate();
		logger.log(Level.INFO,result+ " records deleted");
		stmt.close();
    	
    	
    }
   
   public IDbAccessObject selectOne(String className,String tableName, IDbAccessObject condition) throws Exception{
	   return this.selectOne(className,tableName, null, condition,null);
   }
   
   public List selectList(String className,String tableName, IDbAccessObject condition) throws Exception{
	   return this.selectList(className,tableName, null, condition, null);
   }
   
   
    public IDbAccessObject selectOne(String className,String tableName,List<String> indicator, IDbAccessObject condition ,SelectClauseBounds bounds) throws Exception {
    	
    	List<IDbAccessObject> resultList =(List<IDbAccessObject>) this.selectOne(className, tableName,indicator, condition, bounds);
    	if(resultList!=null && resultList.size()==1){
    		return resultList.get(0);
    	}
    	else if(resultList==null){
    		return null;
    	}
    	else{
    		throw new Exception("More than one record found");
    	}
  
    }
    public List selectList(String className,String tableName, List<String>  indicatorList, IDbAccessObject condition , SelectClauseBounds bounds) throws SQLException, Exception {
    	
    	String indicator="*".intern();
    	if(indicatorList!=null && indicatorList.size()>0){
    		//get the concatenated list
    		StringBuffer indBuf= new StringBuffer();
    		for(String str:indicatorList){
    			indBuf.append(",").append(str);
    		}
    		indicator=indBuf.toString().substring(1);
    	}
    	
    	StringBuffer selectStr=new StringBuffer("SELECT ").append(indicator).append(" FROM ").append(tableName);
    	if(condition!=null){
    		selectStr.append(" WHERE ");
    		selectStr.append(getStatement(condition.getInput()," AND "," LIKE "));
    	}
    
  	
    	PreparedStatement stmnt =connection.prepareStatement(selectStr.toString());
    	
    	if(condition!=null && condition.getInput()!=null){
	    	int i=1;
			//populate the statement parameters
			for( DbProperty prop:condition.getInput()){		
				stmnt.setObject(i++, prop.getValue());
			}
    	}
    	
    	
    	logger.log(Level.INFO,"Executing query: "+stmnt.toString());
    	
    	ResultSet result=stmnt.executeQuery();		
		List<DbProperty> propList=getPropertyListForClass(className);
		List<DbProperty> resultList=null;
		
         Class clazz= Class.forName(className);
		List resultObjList =new ArrayList();
		IDbAccessObject obj=null;
		
		while(result.next()) {
			 
			 resultList = new ArrayList<DbProperty>();
			 for(DbProperty prop:propList){
				 Object objVal=null;
				 try{
					 objVal =result.getObject(prop.getPropertyName());
				 }
				 catch(Exception e){
					 logger.log(Level.WARNING,prop.getPropertyName()+" property not found for the table");
					//Since this property is not in table, correct the list
					 propList.remove(prop);
					 continue;
				 }
				 
				 resultList.add(new DbProperty(prop.getPropertyName(),objVal));
			 }
			 obj= (IDbAccessObject) clazz.newInstance();
			 obj.setObject(resultList);
			 resultObjList.add(obj);
			 
		 }
		
		stmnt.close();
		
		return resultObjList;
    }
	
	
    /**
     * This method gets the property list for the given class using reflection
     * @param className
     * @return
     * @throws IntrospectionException 
     * @throws ClassNotFoundException 
     */
    private List<DbProperty> getPropertyListForClass(String className) throws ClassNotFoundException, IntrospectionException {
		
    	if(ObjectMap.containsKey(className)){
    		return ObjectMap.get(className);
    	}
    	else{
    	
    	 BeanInfo beanInfo = Introspector.getBeanInfo(Class.forName(className));
    	 PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();
    	List<DbProperty> propList=new ArrayList<DbProperty>();
    	 for (PropertyDescriptor pd : pds) {
    	      String propertyName = pd.getName();    	    
    	      if(!(propertyName.equalsIgnoreCase("class".intern())||propertyName.equalsIgnoreCase("input".intern())||propertyName.equalsIgnoreCase("object".intern())||propertyName.equalsIgnoreCase("autoIncrementKey".intern()))){
    	    	  propList.add(new DbProperty(propertyName,null));
    	      }
		
    	  }
    	 ObjectMap.put(className, propList);
    	 
    	 return propList;
    	}
    	 
	}



	/**
     * This method runs independent UPDATE/DELETE/INSERT queries
     * @param query
     * @throws SQLException
     */
	public int runUpdateQuery(String query) throws SQLException{
		Statement stmt = connection.createStatement();
		logger.log(Level.INFO,"Executing query: "+query);
		int result=stmt.executeUpdate(query);
		logger.log(Level.INFO,result+ " records deleted");
		stmt.close();
		return result;
		
	}
	
	
	/**
	 * This method runs independent select queries
	 * @param query
	 * @return
	 * @throws SQLException
	 */
	public List runSelectQuery(String query) throws SQLException{
		
		PreparedStatement stmt = connection.prepareStatement(query);
		logger.log(Level.INFO,"Executing query: "+query);
		ResultSet rslt=stmt.executeQuery();
		ResultSetMetaData metaData =rslt.getMetaData();
		int colCount =metaData.getColumnCount();
		List<DbProperty> propList=new ArrayList<DbProperty>();
		//get all the column names
		for(int counter=1;counter<=colCount;counter++){
			 propList.add(new DbProperty(metaData.getColumnName(counter),null));
		}
		List<Map<String, Object>> resultList =new ArrayList<Map<String, Object>>();
		Map<String, Object> map= null;
		while(rslt.next()) {
			 
			map= new HashMap<String, Object>();
			 for(DbProperty prop:propList){
				 Object objVal=null;
				 try{
					 objVal =rslt.getObject(prop.getPropertyName());
				 }
				 catch(Exception e){
					 logger.log(Level.WARNING,prop.getPropertyName()+" property not found for the table");
					 continue;
				 }
				 
				 map.put(prop.getPropertyName(),objVal);
			 }
			 resultList.add(map);	 
		 }	
		stmt.close();
		return resultList;
	}
	
	public List runSelectQuery(String className, String query) throws Exception{
		
		PreparedStatement stmt = connection.prepareStatement(query);
		logger.log(Level.INFO,"Executing query: "+query);
		ResultSet result=stmt.executeQuery();
		List<DbProperty> propList=getPropertyListForClass(className);
		List<DbProperty> resultList=null;
		
         Class clazz= Class.forName(className);
		List resultObjList =new ArrayList();
		IDbAccessObject obj=null;
		
		while(result.next()) {
			 
			 resultList = new ArrayList<DbProperty>();
			 for(DbProperty prop:propList){
				 Object objVal=null;
				 try{
					 objVal =result.getObject(prop.getPropertyName());
				 }
				 catch(Exception e){
					 logger.log(Level.WARNING,prop.getPropertyName()+" property not found for the table");
					//Since this property is not in table, correct the list
					 propList.remove(prop);
					 continue;
				 }
				 
				 resultList.add(new DbProperty(prop.getPropertyName(),objVal));
			 }
			 obj= (IDbAccessObject) clazz.newInstance();
			 obj.setObject(resultList);
			 resultObjList.add(obj);	 
		 }
		
		stmt.close();
		
		return resultObjList;
	}
	

}
